package com.mr.qa.dao.impl;

import java.util.List;

import org.hibernate.Query;

public class ReportDAO extends BaseHibernateDAO {

	/*
	 * sql:select u.user_id, questionCount.qc, answerCount.ac, sharedFileCount.sc, recommendCount.rc from(
  mr_iask_user u
  left join
  (
	select count(*) qc, q.user_id user_id from mr_iask_question q
	inner join mr_iask_user u on q.user_id = u.user_id
	inner join mr_iask_category c on q.category_id = c.category_id
	where q.state != '4'
		and c.onlyforadmin = 'N'
		and q.post_date >= '2009-01-01 00:00:00'
		and q.post_date <= '2009-09-30 23:59:59'
	group by q.user_id
	) questionCount on u.user_id = questionCount.user_id
  left join
	(
	select count(*) ac, a.user_id user_id from mr_iask_answer a
	inner join mr_iask_user u on a.user_id = u.user_id
	inner join mr_iask_question q on a.question_id = q.question_id
	inner join mr_iask_category c on q.category_id = c.category_id
	where c.onlyforadmin = 'N'
  	and c.state != '1'
  	and q.state != '4'
  	and a.is_Anonymous = 0
  	and a.post_date >= '2009-01-01 00:00:00'
		and a.post_date <= '2009-09-30 23:59:59'
	group by a.user_id
	) answerCount on u.user_id = answerCount.user_id
	left join
	(
	select count(*) sc, s.user_id user_id from mr_iask_shared_file s
  where s.state != '1'
  	and s.post_date >= '2009-01-01 00:00:00'
		and s.post_date <= '2009-09-30 23:59:59'
	group by s.user_id
  ) sharedFileCount on u.user_id = sharedFileCount.user_id
  left join
  (
  select count(q.question_id) rc, q.user_id user_id from mr_iask_question q
	inner join mr_iask_user_score_history h on h.resource_id = q.question_id
	where h.score_type = 'recommend'
  	and (q.state = '1' || q.state = '5' )
  	and h.score_date > (select IFNULL(max(score_date), '1900-01-01') from mr_iask_user_score_history h1 where h1.score_type='unrecommend' and h1.resource_id = h.resource_id)
  	and h.score_date >= '2009-01-01 00:00:00'
  	and h.score_date <= '2010-12-30 23:59:59'
	group by user_id
	) recommendCount on u.user_id = recommendCount.user_id
)
	 */
	public List countQuestionAnswerSharedRecommnd(String userIds,
			String fromDate, String toDate) {
		String dateFromClause = " >= '" + fromDate + "'";
		String dateToClause = " <= '" + toDate + "'";
		StringBuffer questionCountSql = new StringBuffer("select count(*) qc, q.user_id user_id from mr_iask_question q ")
				.append("inner join mr_iask_user u on q.user_id = u.user_id ")
				.append("inner join mr_iask_category c on q.category_id = c.category_id ")
				.append("where q.state != '4' and c.onlyforadmin = 'N' ")
				.append("and q.post_date").append(dateFromClause).append(" and q.post_date").append(dateToClause).append(" group by q.user_id");
		StringBuffer answerCountSql = new StringBuffer("select count(*) ac, a.user_id user_id from mr_iask_answer a ")
				.append("inner join mr_iask_user u on a.user_id = u.user_id ")
				.append("inner join mr_iask_question q on a.question_id = q.question_id ")
				.append("inner join mr_iask_category c on q.category_id = c.category_id ")
				.append("where c.onlyforadmin = 'N' and c.state != '1' and q.state != '4' and a.is_Anonymous = 0 ")
				.append("and a.post_date").append(dateFromClause).append(" and a.post_date").append(dateToClause).append(" group by a.user_id");
		StringBuffer sharedFileSql = new StringBuffer("select count(*) sc, s.user_id user_id from mr_iask_shared_file s ")
				.append("where s.state != '1' and s.post_date").append(dateFromClause).append(" and s.post_date").append(dateToClause).append(" group by s.user_id");
		StringBuffer remmendSql = new StringBuffer("select count(q.question_id) rc, q.user_id user_id from mr_iask_question q ")
				.append("inner join mr_iask_user_score_history h on h.resource_id = q.question_id ")
				.append("where h.score_type = 'recommend' and (q.state = '1' || q.state = '5' ) ")
				.append("and h.score_date > (select IFNULL(max(score_date), '1900-01-01') from mr_iask_user_score_history h1 where h1.score_type='unrecommend' and h1.resource_id = h.resource_id) ")
				.append("and h.score_date").append(dateFromClause).append(" and h.score_date").append(dateToClause).append(" group by user_id");
		StringBuffer sql = new StringBuffer("select u.user_id, u.login_name, IFNULL(questionCount.qc,0), IFNULL(answerCount.ac,0), IFNULL(sharedFileCount.sc,0), IFNULL(recommendCount.rc,0) from( ")
				.append("mr_iask_user u left join ( ").append(questionCountSql).append(" ) questionCount on u.user_id = questionCount.user_id ")
				.append("left join (").append(answerCountSql).append(" ) answerCount on u.user_id = answerCount.user_id ")
				.append("left join (").append(sharedFileSql).append(") sharedFileCount on u.user_id = sharedFileCount.user_id ")
				.append("left join (").append(remmendSql).append(" ) recommendCount on u.user_id = recommendCount.user_id)");
		if(userIds.length() > 0){
			sql.append("where u.login_name in (").append(userIds).append(")");
		}
		Query query = getSession().createSQLQuery(sql.toString());
		//System.out.println(sql);
		return query.list();
	}
}
